<?php
/**
 * Created by PhpStorm.
 * User: clkj1
 * Date: 2018/7/25
 * Time: 9:45
 */

class AdminSql
{

    protected $conn;
    protected $res;

    function __construct()
    {
        // 1.链接数据库
        if ($_SERVER['HTTP_HOST'] == 'poster.hdacgn.com') $passwd = '1935201a7515b830'; else $passwd = 'root';
        $conn = @new mysqli('localhost', 'root', $passwd, 'poster_system');
        if ($conn->connect_error) {//返回链接错误号
            // 返回链接错误信息
            die('数据库链接错误 (' . $conn->connect_errno . ') '
                . $conn->connect_error);
        }
        // 2. 选择数据库
        $conn->select_db("poster_system") or die("选择数据库失败：" . $conn->error);
        $conn->set_charset('utf8');
        $conn->query("set character set 'utf8'");
        $conn->query("set names 'utf8'");
        $this->conn = $conn;
    }

    public function signin($email, $password)
    {
        $email = addslashes($email);
        $password = addslashes(sha1($password));
        $time = time();
        $sql_search = "SELECT 1 FROM tbl_users WHERE us_Email = {$email}";
        $res_search = $this->conn->query($sql_search);
        if ($res_search) {
            $row_search = $res_search->fetch_row();
            if ($row_search > 0) {
                return 1;
            }
        }
        $sql = "INSERT INTO tbl_users(us_PassWord, us_UserName, us_Rank, us_Email, us_CreateTime, us_LastLoginTime, us_LoginTries, us_isLogin, us_ClockTime)VALUES('$password', '$email', 0, '$email', $time, 0, 0, 0, 0)";
        $res = $this->conn->query($sql);
        if ($res) {
            return 0;
        } else {
            return $this->conn->error;
        }
    }

    // 0, [uid] 没问题 登录成功 1, [error] 查询错误 2 用户不存在 3, [tries] 密码错误，剩余尝试次数 4, [time] 尝试次数到了，锁定到时间
    public function login($email, $password)
    {
        $email = addslashes($email);
        $s_password = sha1($password);
        $time = time();
        $clock_time = $time + 3600;
        $sql = "SELECT pk_users_id, us_PassWord, us_LoginTries, us_UserName, us_ClockTime FROM tbl_users WHERE us_Email = '$email'";
        $res = $this->conn->query($sql);
        if ($res) {
            $row = $res->fetch_row();
            if (!empty($row)) {
                // 如果结果不是空的
                $real_password = $row[1];
                $login_tries = $row[2];
                $login_clock = $row[4];
                if ($time < $login_clock) {
                    // 如果目前时间小于锁定时间 说明被锁定
                    return array(4, $login_clock);
                } else {
                    // 如果目前时间 大于锁定时间， 没锁定
                    if ($login_tries < 3) {
                        // 如果尝试次数 <3 进行验证
                        if (hash_equals($real_password, $s_password)) {
                            // 如果密码验证成功
                            $sql_update = "UPDATE tbl_users SET us_LastLoginTime = $time, us_isLogin = 1, us_LoginTries = 0 WHERE us_Email = '$email'";
                            $this->conn->query($sql_update);
                            return array(0, $row[0], $row[3]);
                        } else {
                            // 如果没成功 则进行+1
                            $sql_update = "UPDATE tbl_users SET us_LoginTries = us_LoginTries + 1 WHERE us_Email = '$email'";
                            $this->conn->query($sql_update);
                            $sql_tries = "SELECT us_LoginTries FROM tbl_users WHERE us_Email = '$email'";
                            $res_tries = $this->conn->query($sql_tries);
                            $row_tries = $res_tries->fetch_row();
                            if ($row_tries[0] < 3) {
                                $tries = 3 - $row_tries[0];
                                return array(3, $tries);
                            } else {
                                // 如果尝试次数 >2 返回锁定时间
                                $sql_clock = "UPDATE tbl_users SET us_ClockTime = $clock_time, us_LoginTries = 0 WHERE us_Email = '$email'";
                                $this->conn->query($sql_clock);
                                return array(4, $clock_time);
                            }
                        }
                    } else {
                        // 如果尝试次数 >2 返回锁定时间
                        $sql_clock = "SELECT us_ClockTime FROM tbl_users WHERE us_Email = '$email'";
                        $res = $this->conn->query($sql_clock);
                        if ($res) {
                            $row = $res->fetch_row();
                            return array(4, $row[0]);
                        } else {
                            return array(1, $this->conn->error);
                        }
                    }
                }
            } else {
                // 结果是空的 用户不存在
                return array(2);
            }
        } else {
            // 查询错误
            return array(1, $this->conn->error);
        }
    }

    public function searchAllUidForms($uid)
    {
        $uid = addslashes($uid);
        $sql = "SELECT DISTINCT ts_TaId, ts_QuCt FROM tbl_tables WHERE ts_UserId = $uid ORDER BY ts_QuCt DESC";
        $res = $this->conn->query($sql);
        $num = $res->num_rows;
        $result_array = array();
        for ($i = 0; $i < $num; $i++) {
            $row = $res->fetch_row();
            $result_array[] = $row[0];
        }
        return array($num, $result_array);
    }

    public function searchAllUidTidForms($uid, $tid, $search = 'all')
    {
        $uid = addslashes($uid);
        $tid = addslashes($tid);
        if ($search === 'all') {
            if ($tid === 0 || $tid === 'all') {
                $sql = "SELECT DISTINCT tc_UserId, tc_QuCt, tc_TaId FROM tbl_table_content WHERE tc_CId = $uid ORDER BY tc_QuCt DESC";
                $res = $this->conn->query($sql);
                if ($res) {
                    $num = $res->num_rows;
                    $m_array = array();
                    for ($i = 0; $i < $num; $i++) {
                        $row = $res->fetch_row();
                        $m_array[] = array($row[0], $row[1], $row [2]);
                    }
                } else {
                    $num = 0;
                    $m_array = array();
                }
            } else {
                $sql = "SELECT DISTINCT tc_UserId, tc_QuCt, tc_TaId FROM tbl_table_content WHERE tc_CId = $uid AND tc_TaId = $tid ORDER BY tc_QuCt DESC";
                $res = $this->conn->query($sql);
                if ($res) {
                    $num = $res->num_rows;
                    $m_array = array();
                    for ($i = 0; $i < $num; $i++) {
                        $row = $res->fetch_row();
                        $m_array[] = array($row[0], $row[1], $row[2]);
                    }
                } else {
                    $num = 0;
                    $m_array = array();
                }
            }
        } else {
            $search = addslashes($search);
            if ($tid === 0 || $tid === 'all') {
                $sql = "SELECT DISTINCT tc_UserId, tc_QuCt, tc_TaId FROM tbl_table_content WHERE tc_CId = $uid AND tc_UserId = '$search' ORDER BY tc_QuCt DESC";
                $res = $this->conn->query($sql);
                if ($res) {
                    $num = $res->num_rows;
                    $m_array = array();
                    for ($i = 0; $i < $num; $i++) {
                        $row = $res->fetch_row();
                        $m_array[] = array($row[0], $row[1], $row [2]);
                    }
                } else {
                    $num = 0;
                    $m_array = array();
                }
            } else {
                $sql = "SELECT DISTINCT tc_UserId, tc_QuCt, tc_TaId FROM tbl_table_content WHERE tc_CId = $uid AND tc_TaId = $tid AND tc_UserId = '$search' ORDER BY tc_QuCt DESC";
                $res = $this->conn->query($sql);
                if ($res) {
                    $num = $res->num_rows;
                    $m_array = array();
                    for ($i = 0; $i < $num; $i++) {
                        $row = $res->fetch_row();
                        $m_array[] = array($row[0], $row[1], $row[2]);
                    }
                } else {
                    $num = 0;
                    $m_array = array();
                }
            }
        }
        return array($num, $m_array);
    }

    public function searchNewForm($uid, $list)
    {
        $uid = addslashes($uid);
        $list = addslashes($list);
        $sql = "SELECT DISTINCT ts_TaId, ts_QuCt FROM tbl_tables WHERE ts_UserId = $uid ORDER BY ts_QuCt DESC";
        $res = $this->conn->query($sql);
        $num = $res->num_rows;
        $result_array = array();
        if ($num > $list) {
            for ($i = 0; $i < $list; $i++) {
                $row = $res->fetch_row();
                $result_array[] = array($row[0], $row[1]);
            }
            return array($list, $result_array);
        } else {
            for ($i = 0; $i < $num; $i++) {
                $row = $res->fetch_row();
                $result_array[] = array($row[0], $row[1]);
            }
            return array($num, $result_array);
        }
    }

    public function searchAddEditForm($uid, $list)
    {
        $uid = addslashes($uid);
        $list = addslashes($list);
        $sql = "SELECT DISTINCT tc_UserId, tc_TaId, tc_QuCt FROM tbl_table_content WHERE tc_CId = $uid ORDER BY tc_QuCt DESC";
        $res = $this->conn->query($sql);
        $num = $res->num_rows;
        $result_array = array();
        if ($num > $list) {
            for ($i = 0; $i < $list; $i++) {
                $row = $res->fetch_row();
                $result_array[] = array($row[0], $row[1], $row[2]);
            }
            return array($list, $result_array);
        } else {
            for ($i = 0; $i < $num; $i++) {
                $row = $res->fetch_row();
                $result_array[] = array($row[0], $row[1], $row[2]);
            }
            return array($num, $result_array);
        }
    }

    public function sPoUrl($uid, $pid)
    {
        $uid = addslashes($uid);
        $pid = addslashes($pid);
        $sql = "SELECT DISTINCT ts_PoIm FROM tbl_tables WHERE ts_UserId = $uid AND ts_TaId = $pid";
        $res = $this->conn->query($sql);
        if ($res) {
            $num = $res->num_rows;
            if ($num > 0) {
                $row = $res->fetch_row();
                return $row[0];
            }
        }
    }

    // 今天报名人数
    public function todayPosts($uid)
    {
        $uid = addslashes($uid);
        date_default_timezone_set('PRC');
        $now_date = date('Y-m-d');
        $now_array = explode('-', $now_date);
        $to_day = mktime(0, 0, 0, $now_array[1], $now_array[2], $now_array[0]);
        $sql = "SELECT COUNT(1) FROM (SELECT DISTINCT tc_UserId FROM tbl_table_content WHERE tc_CId = $uid AND tc_QuCt > $to_day) a";
        $res = $this->conn->query($sql);
        if ($res) {
            $row = $res->fetch_row();
            return $row[0];
        } else {
            return $this->conn->error;
        }
    }

    // 累计报名人数
    public function allPosts($uid)
    {
        $uid = addslashes($uid);
        $sql = "SELECT COUNT(1) FROM (SELECT DISTINCT tc_UserId FROM tbl_table_content WHERE tc_CId = $uid) a";
        $res = $this->conn->query($sql);
        if ($res) {
            $row = $res->fetch_row();
            return $row[0];
        } else {
            return $this->conn->error;
        }
    }

    /**
     * @param $line
     * @param $uid
     * @return array
     */
    public function searchPosters($line, $uid)
    {
        $how = 100;
        $line = addslashes($line);
        $uid = addslashes($uid);
        date_default_timezone_set('PRC');
        $now_date = date('Y-m-d');
        $now_array = explode('-', $now_date);
        $to_day = mktime(0, 0, 0, $now_array[1], $now_array[2], $now_array[0]);

        $array = [];
        // 有多少海报：
        $sql = "SELECT COUNT(1) FROM (SELECT DISTINCT ts_TaId FROM tbl_tables WHERE ts_UserId = $uid) a";
        $res = $this->conn->query($sql);
        if ($res) {
            $row = $res->fetch_row();
            $num = $row[0];
            $start = $line * $how;
            if ($num - $start <= $how) {
                $now_limit = $num - $start;
                $sql = "SELECT DISTINCT ts_TaId, ts_title, ts_QuCt, ts_PoIm FROM tbl_tables WHERE ts_UserId = $uid ORDER BY ts_QuCt DESC LIMIT $start, $now_limit";
                $res = $this->conn->query($sql);
                if ($res) {
                    for ($i = 0; $i < $num; $i++) {
                        $row = $res->fetch_row();
                        $array[] = [$row[0], $row[1], $row[2], $row[3]];
                    }
                    return array(0, $array);// 0 没了
                } else
                    return array(2, $this->conn->error);
            } else {
                $sql = "SELECT DISTINCT ts_TaId, ts_title, ts_QuCt, ts_PoIm FROM tbl_tables WHERE ts_UserId = $uid ORDER BY ts_QuCt DESC LIMIT $start, $how";
                $res = $this->conn->query($sql);
                if ($res) {
                    for ($i = 0; $i < $how; $i++) {
                        $row = $res->fetch_row();
                        $array[] = [$row[0], $row[1], $row[2], $row[3]];
                    }
                    return array(1, $array);// 1 还有
                }
            }
        } else
            return array(2, $this->conn->error);
    }

    // 查询某表格有多少人报名了：
    public function posterPosts($pid, $cid)
    {
        $pid = addslashes($pid);
        $cid = addslashes($cid);
        $sql = "SELECT COUNT(1) FROM (SELECT DISTINCT tc_UserId FROM tbl_table_content WHERE tc_CId = '$cid' AND tc_TaId = '$pid') a";
        $res = $this->conn->query($sql);
        if ($res) {
            $row = $res->fetch_row();
            return $row[0];
        } else
            return $this->conn->error;
    }

    // 查询user的sets
    public function searchSets($uid)
    {
        $uid = addslashes($uid);
        $sql = "SELECT us_sets FROM tbl_users WHERE pk_users_id = '$uid'";
        $res = $this->conn->query($sql);
        if ($res) {
            $row = $res->fetch_row();
            $res_array = json_decode($row[0], true);
            return [0, $res_array];
        } else {
            return [1, $this->conn->error];
        }
    }

    // 设置用户设置
    public function setUserSets($uid, $mode, $json)
    {
        $uid = addslashes($uid);
        $doubleListArray = json_decode($json, true);
        $listArray = array_combine($doubleListArray[0], $doubleListArray[1]);
        $originArrayArray = $this->searchSets($uid);
        $originArray = $originArrayArray[1];
        $originArray[$mode] = $listArray;
        $changeJson = addslashes(json_encode($originArray, JSON_UNESCAPED_UNICODE));
        $sql = "UPDATE tbl_users SET us_sets = '$changeJson' WHERE pk_users_id = $uid";
        $res = $this->conn->query($sql);
        if ($res) {
            return 0;
        } else {
            return $this->conn->error;
        }
    }

    // 根据表格id查出海报图片：
    public function searchImFromTaId($uid, $taId)
    {
        $uid = addslashes($uid);
        $taId = addslashes($taId);
        $sql = "SELECT DISTINCT ts_PoIm FROM tbl_tables WHERE ts_TaId = $taId AND ts_UserId = $uid";
        $res = $this->conn->query($sql);
        if ($res) {
            $num = $res->num_rows;
            if ($num !== 0) {
                $row = $res->fetch_row();
                return [0, $row[0]];
            } else {
                return [1];
            }
        } else {
            return $this->conn->error;
        }
    }

    // 根据表格id查出填写的所有用户
    public function searchUsersFromTaId($uid, $tid)
    {
        $uid = addslashes($uid);
        $tid = addslashes($tid);
        $sql = "SELECT DISTINCT tc_UserId, tc_QuCt FROM tbl_table_content WHERE tc_TaId = $tid AND tc_CId = $uid ORDER BY tc_QuCt DESC ";
        $res = $this->conn->query($sql);
        if ($res) {
            $res_array = [];
            $num = $res->num_rows;
            for ($i = 0; $i < $num; $i++) {
                $row = $res->fetch_row();
                $res_array[] = $row[0];
            }
            return [0, $num, $res_array];
        } else {
            return [1, $this->sqlError()];
        }
    }

    // 查询用户填写信息
    public function searchUsersForm($userId, $uid, $tid)
    {
        $userId = addslashes($userId);
        $uid = addslashes($uid);
        $tid = addslashes($tid);
        $sql = "SELECT tc_QuName, tc_QuAnswer FROM tbl_table_content WHERE tc_UserId = '$userId' AND tc_CId = $uid AND tc_TaId = $tid ORDER BY pk_table_content_id ASC";
        $res = $this->conn->query($sql);
        if ($res) {
            $quName = [];
            $quAnswer = [];
            $num = $res->num_rows;
            if ($num === 0) {
                return [1, []];
            }
            for ($i = 0; $i < $num; $i++) {
                $row = $res->fetch_row();
                $quName[] = $row[0];
                $quAnswerJson = $row[1];
                $quAnswer[] = json_decode($quAnswerJson, true);
            }
            $formArray = array_combine($quName, $quAnswer);
            return [0, $formArray];
        } else {
            return [1, $this->sqlError()];
        }
    }

    // 修改用户信息
    public function editUserSets($uid, $type, $json)
    {
        $uid = addslashes($uid);
        $type = addslashes($type);
        $sql = "SELECT us_sets FROM tbl_users WHERE pk_users_id = $uid";
        $res = $this->conn->query($sql);
        if ($res) {
            $row = $res->fetch_row();
            $sets_json = $row[0];
            $sets_array = json_decode($sets_json, true);
            $g_array = json_decode($json, true);
            $sets_array[$type] = $g_array;
            $sets_json = addslashes(json_encode($sets_json, JSON_UNESCAPED_UNICODE));
            $sql_update = "UPDATE tbl_users SET us_sets = '$sets_json' WHERE pk_users_id = '$uid'";
            $res_update = $this->conn->query($sql_update);
            if ($res_update) {
                return 0;
            } else return $this->sqlError();
        } else return $this->sqlError();
    }

    // 删除海报
    public function deletePoster($uid, $pid)
    {
        $uid = addslashes($uid);
        $pid = addslashes($pid);
        $sql_search = "SELECT COUNT(1) FROM tbl_tables WHERE ts_TaId = '$pid' AND ts_UserId = '$uid'";
        if ($res_search = $this->conn->query($sql_search)) {
            $num = $res_search->num_rows;
            if ($num == 1) {
                $sql_delete = "DELETE FROM tbl_tables WHERE ts_TaId = '$pid' AND ts_UserId = '$uid';";
                $sql_delete .= "DELETE FROM tbl_table_content WHERE tc_TaId = '$pid' AND tc_CId = '$uid'";
                if ($this->conn->multi_query($sql_delete)) return 0; else return $this->sqlError();
            } else  return 1;
        } else return $this->sqlError();
    }

    public function deleteApply2($uid, $tid, $json)
    {
        $uid = addslashes($uid);
        $tid = addslashes($tid);
        $userArray = json_decode($json, true);

        $sql_search = "SELECT DISTINCT tc_UserId FROM tbl_table_content WHERE tc_CId = '$uid' AND tc_TaId = '$tid'";
        $res_search = $this->conn->query($sql_search);
        if ($res_search) {
            $num_search = $res_search->num_rows;
            $row_search_array = [];
            for ($i = 0; $i < $num_search; $i++) {
                $row_search = $res_search->fetch_row();
                $row_search_array[] = $row_search[0];
            }
            $delete_array = [];
            foreach ($row_search_array as $item) {
                if (in_array($item, $userArray)) {
                    $delete_array[] = $item;
                }
            }
            if ($count_delete = count($delete_array) > 0) {
                $delete_string = '';
                $j = 0;
                foreach ($delete_array as $item) {
                    $j++;
                    if ($j != $count_delete)
                        $delete_string .= addslashes($item) . ',';
                    else
                        $delete_string .= addslashes($item);
                }
                $delete_string = addslashes($delete_string);
                $sql_delete = "DELETE FROM tbl_table_content WHERE tc_CId = '$uid' AND tc_TaId = '$tid' AND tc_UserId IN ($delete_string)";
                $res_delete = $this->conn->query($sql_delete);
                if ($res_delete) {
                    return 0;
                } else return 4;
            } else return 2;
        } else return 3;
    }

    public function deleteApply3($uid, $tid, $pid)
    {
        $uid = addslashes($uid);
        $tid = addslashes($tid);
        $pid = addslashes($pid);
        $sql_delete = "DELETE FROM tbl_table_content WHERE tc_CId = '$uid' AND tc_TaId = '$tid' AND tc_UserId = '$pid'";
        $res_delete = $this->conn->query($sql_delete);
        if ($res_delete) return 0; else return 1;
    }

    // 删除未保存的数据
    public function deleteTempPoster($uid, $fid)
    {
        $uid = addslashes($uid);
        $fid = addslashes($fid);
        $sql_delete = "DELETE FROM tbl_tables WHERE ts_UserId = $uid AND ts_TaId = $fid;DELETE FROM tbl_table_content WHERE tc_CId = $uid AND tc_TaId = $fid";
        $res = $this->conn->multi_query($sql_delete);
        return $res ? 0 : 1;
    }

    private function sqlError()
    {
        return $this->conn->error;
    }

    function __destruct()
    {
        // TODO: Implement __destruct() method.
        // 10.释放结果集资源
        if (isset($this->res) && !is_bool($this->res))
            $this->res->close();// $res->free();
        // 关闭数据库连接
        $this->conn->close();
    }
}